﻿/***********************************************************************************************************************************
 *    Name: 
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE Forcasting;
GO

ALTER VIEW [dbo].[DWHistory] AS WITH
	e AS (SELECT me.ManagedEntityDefaultName, me.ManagedEntityRowId
	  FROM OperationsManagerDW.dbo.ManagedEntityType met
		INNER JOIN OperationsManagerDW.dbo.ManagedEntity me ON (met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId)
		WHERE met.ManagedEntityTypeSystemName IN (
			N'Microsoft.SystemCenter.VirtualMachineManager.2012.ESXHost',
			N'Microsoft.Windows.Computer',N'OpsLogix.IMP.VMWare.ESXHost',
			N'Veeam.Virt.Extensions.VMware.VMHOST',
			N'Veeam.Virt.Extensions.VMware.VMwareVirtualMachine')),
	r AS (SELECT pr.ObjectName, pr.CounterName, pri.InstanceName, pri.PerformanceRuleInstanceRowId
	  FROM OperationsManagerDW.dbo.PerformanceRule pr
		INNER JOIN OperationsManagerDW.dbo.PerformanceRuleInstance pri ON (pr.RuleRowId = pri.RuleRowId))
  SELECT
	e.ManagedEntityDefaultName AS EntityName,
	r.ObjectName, r.CounterName, r.InstanceName,
	p.DateTime, p.SampleCount, p.AverageValue, p.MinValue, p.MaxValue, p.StandardDeviation
  FROM OperationsManagerDW.perf.vPerfDaily p INNER JOIN e ON (p.ManagedEntityRowId = e.ManagedEntityRowId) INNER JOIN r ON (p.PerformanceRuleInstanceRowId = r.PerformanceRuleInstanceRowId);
GO

DROP TABLE dbo.Usage_Metrics;
CREATE TABLE dbo.Usage_Metrics (
	EntityName			NVARCHAR(128)	NOT Null,
	ObjectName			NVARCHAR(128)	NOT Null,
	CounterName			NVARCHAR(128)	NOT NULL,
	InstanceName		NVARCHAR(128)	NOT NULL,
	DateTime			DATETIME		NOT NULL,
	SampleCount			INT				NOT NULL,
	AverageValue		FLOAT			NOT NULL,
	MinValue			FLOAT			NOT NULL,
	MaxValue			FLOAT			NOT NULL,
	StandardDeviation	FLOAT			NOT NULL,
	CONSTRAINT PK_UsageMetrics PRIMARY KEY CLUSTERED (EntityName,ObjectName,CounterName,InstanceName,DateTime));
CREATE NONCLUSTERED INDEX IX_DateTime ON dbo.Usage_Metrics (DateTime);
GO

MERGE dbo.Trend_Data AS t
  USING (SELECT * FROM dbo.DWHistory) AS s ON (
		t.EntityName	= s.EntityName		AND
		t.ObjectName	= s.ObjectName		AND
		t.CounterName	= s.CounterName		AND
		t.InstanceName	= s.InstanceName	AND
		t.DateTime		= s.DateTime		)
  WHEN NOT MATCHED THEN
	INSERT (  EntityName,   ObjectName,   CounterName,   InstanceName,   DateTime,   SampleCount,   AverageValue,   MinValue,   MaxValue,   StandardDeviation)
	VALUES (s.EntityName, s.ObjectName, s.CounterName, s.InstanceName, s.DateTime, s.SampleCount, s.AverageValue, s.MinValue, s.MaxValue, s.StandardDeviation);
GO